//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

/* Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


using System;
using System.Collections.Generic;
using System.Text;
using EffiProzDB.Lib;



namespace EffiProzDB
{

    /**
     * The methods _in this class perform alterations to the structure of an
     * existing table which may result _in a new Table object
     *
     * @author fredt@users
     * @version 1.8.0
     * @since 1.7.0
     */
   public class TableWorks
    {

        private Table table;
        private Session session;

       public  TableWorks(Session session, Table table)
        {
            this.table = table;
            this.session = session;
        }

       public Table getTable()
       {
           return table;
       }

       /**
        *  Creates a foreign key according to current sql.strict_fk or
        *  sql.strong_fk settings. Foreign keys are enforced via indexes on both
        *  the referencing (child) and referenced (parent) tables.
        *  <p>
        *  In versions 1.7.0 and 1.7.1 some non-standard features were supported
        *  for compatibility with older databases. These allowed foreign keys
        *  to be created without the prior existence of a unique constraint on
        *  the referenced columns.
        *  <p>
        *  In version 1.7.2, a unique constraint on the referenced columns must
        *  exist.
        *
        *  The non-unique index on the referencing table is now always created
        *  whether or not a PK or unique constraint index on the columns exist.
        *  This closes the loopholes opened by the introduction of ALTER TABLE
        *  for adding foreign keys.
        *
        *  Foriegn keys on temp tables can reference other temp tables with the
        *  same rules above. Foreign keys on permanent tables cannot reference
        *  temp tables.
        *
        *  Duplicate foreign keys are now disallowed.
        *
        *  -- The unique index on the referenced table must always belong to a
        *  constraint (PK or UNIQUE). Otherwise after a SHUTDOWN and restart the
        *  index will not exist at the time of creation of the foreign key when
        *  the foreign key is referencing the  same table.
        *
        *  -- The non-unique index on the referencing table is always created
        *  regardless of any existing index. This allows the foreign key
        *  constraint to be dropped when required.
        *
        *
        *  (fred@users)
        *
        * @param  fkcol
        * @param  expcol
        * @param  name         foreign key name
        * @param  expTable
        * @param  deleteAction
        * @param  updateAction
        * @ 
        */
       public void createForeignKey(int[] fkcol, int[] expcol, HsqlNameManager.HsqlName name,
                             Table mainTable, int deleteAction,
                             int updateAction)
       {

           table.database.schemaManager.checkConstraintExists(name.name,
                   table.getSchemaName(), false);

           // name check
           if (table.getConstraint(name.name) != null)
           {
               throw Trace.error(Trace.CONSTRAINT_ALREADY_EXISTS);
           }

           // existing FK check
           if (table.getConstraintForColumns(mainTable, expcol, fkcol) != null)
           {
               throw Trace.error(Trace.CONSTRAINT_ALREADY_EXISTS);
           }

           if (mainTable.isTemp() != table.isTemp())
           {
               throw Trace.error(Trace.FOREIGN_KEY_NOT_ALLOWED);
           }

           bool isSelf = table == mainTable;
           int offset = table.database.schemaManager.getTableIndex(table);
           bool isforward =
               offset != -1
               && offset < table.database.schemaManager.getTableIndex(mainTable);
           Index exportindex =
               mainTable.getUniqueConstraintIndexForColumns(expcol);

           if (exportindex == null)
           {
               throw Trace.error(Trace.SQL_CONSTRAINT_REQUIRED,
                                 mainTable.getName().statementName);
           }

           // existing rows, value checks
           Constraint.checkReferencedRows(session, table, fkcol, exportindex);

           // create
           HsqlNameManager.HsqlName iname = table.database.nameManager.newAutoName("IDX");
           Index fkindex = createIndex(fkcol, iname, false, true, isforward);
           HsqlNameManager.HsqlName pkname = table.database.nameManager.newAutoName("REF",
               name.name);

           if (isSelf)
           {

               // _in case createIndex resulted _in new Table object
               mainTable = table;
           }

           Constraint c = new Constraint(pkname, name, mainTable, table, expcol,
                                         fkcol, exportindex, fkindex,
                                         deleteAction, updateAction);

           table.addConstraint(c);
           mainTable.addConstraint(new Constraint(pkname, c));
           table.database.schemaManager.registerConstraintName(name.name,
                   table.getName());
       }

       /**
        *  Because of the way indexes and column data are held _in memory and
        *  on disk, it is necessary to recreate the table when an index is added
        *  to a non-empty table cached table.<p>
        *
        *  With empty tables, Index objects are simply added<p>
        *
        *  With MEOMRY and TEXT tables, a new index is built up and nodes for
        *  earch row are interlinked (fredt@users)
        *
        * @param  col
        * @param  name
        * @param  unique
        * @param constraint
        * @param forward
        * @return  new index
        * @throws  HsqlException normally for lack of resources
        */
       public Index createIndex(int[] col, HsqlNameManager.HsqlName name, bool unique,
                         bool constraint,
                         bool forward)
       {

           Index newindex;

           if (table.isEmpty(session) || table.isIndexingMutable())
           {
               newindex = table.createIndex(session, col, name, unique,
                                            constraint, forward);

               table.database.schemaManager.clearTempTables(session, table);
           }
           else
           {
               Table tn = table.moveDefinition(null, null, -1, 0);

               newindex = tn.createIndexStructure(col, name, unique, constraint,
                                                  forward);

               tn.moveData(session, table, -1, 0);
               tn.updateConstraintsTables(session, table, -1, 0);

               int index = table.database.schemaManager.getTableIndex(table);

               table.database.schemaManager.setTable(index, tn);

               table = tn;
           }

           table.database.schemaManager.clearTempTables(session, table);
           table.database.schemaManager.registerIndexName(
               newindex.getName().name, table.getName());
           table.database.schemaManager.recompileViews(table);

           return newindex;
       }

       public void addPrimaryKey(int[] cols, HsqlNameManager.HsqlName name)
       {

           if (name == null)
           {
               name = table.makeSysPKName();
           }

           table.database.schemaManager.checkConstraintExists(name.name,
                   table.getSchemaName(), false);
           addOrDropPrimaryKey(cols, false);

           Constraint newconstraint = new Constraint(name, table,
               table.getPrimaryIndex(), Constraint.PRIMARY_KEY);

           table.addConstraint(newconstraint);
           table.database.schemaManager.registerConstraintName(name.name,
                   table.getName());
       }

       public void addOrDropPrimaryKey(int[] cols,
                                bool identity)
       {

           if (cols == null)
           {
               table.checkDropIndex(table.getIndexes()[0].getName().name, null,
                                    true);
           }

           Table tn = table.moveDefinitionPK(cols, identity);

           tn.moveData(session, table, -1, 0);
           tn.updateConstraintsTables(session, table, -1, 0);

           int index = table.database.schemaManager.getTableIndex(table);

           table.database.schemaManager.setTable(index, tn);

           table = tn;

           table.database.schemaManager.recompileViews(table);
       }

       /**
        *  A unique constraint relies on a unique indexe on the table. It can
        *  cover a single column or multiple columns.
        *  <p>
        *  All unique constraint names are generated by Database.java as unique
        *  within the database. Duplicate constraints (more than one unique
        *  constriant on the same set of columns are still allowed but the
        *  names will be different. (fredt@users)
        *
        * @param  col
        * @param  name
        * @throws  HsqlException
        */
       public void createUniqueConstraint(int[] col,
                                   HsqlNameManager.HsqlName name)
       {

           table.database.schemaManager.checkConstraintExists(name.name,
                   table.getSchemaName(), false);

           Constraint[] constraints = table.getConstraints();

           for (int i = 0, size = constraints.Length; i < size; i++)
           {
               Constraint c = constraints[i];

               if (c.isEquivalent(col, Constraint.UNIQUE)
                       || c.getName().name.Equals(name.name))
               {
                   throw Trace.error(Trace.CONSTRAINT_ALREADY_EXISTS);
               }
           }

           // create an autonamed index
           HsqlNameManager.HsqlName indexname = table.database.nameManager.newAutoName("IDX",
               name.name);
           Index index = createIndex(col, indexname, true, true, false);
           Constraint newconstraint = new Constraint(name, table, index,
               Constraint.UNIQUE);

           table.addConstraint(newconstraint);
           table.database.schemaManager.registerConstraintName(name.name,
                   table.getName());
       }

       public void createCheckConstraint(Constraint c,
                                  HsqlNameManager.HsqlName name)
       {

           table.database.schemaManager.checkConstraintExists(name.name,
                   table.getSchemaName(), false);

           // check the existing rows
           Expression e = c.core.check;

           // this workaround is here to stop LIKE optimisation (for proper scripting)
           e.setLikeOptimised();

           Select s = Expression.getCheckSelect(session, table, e);
           Result r = s.getResult(session, 1);

           c.core.checkFilter = s.tFilter[0];
           c.core.mainTable = table;

           if (r.getSize() != 0)
           {
               throw Trace.error(Trace.CHECK_CONSTRAINT_VIOLATION);
           }

           // getDDL() is here to ensure no subselects etc. are _in condition
           e.getDDL();

           // removes reference to the Index object _in filter
           c.core.checkFilter.setAsCheckFilter();
           table.addConstraint(c);
           table.database.schemaManager.registerConstraintName(name.name,
                   table.getName());
       }

       /**
        *  Because of the way indexes and column data are held _in memory and
        *  on disk, it is necessary to recreate the table when an index is added
        *  to a non-empty table.<p>
        *  Originally, this method would break existing foreign keys as the
        *  table order _in the DB was changed. The new table is now linked
        *  _in place of the old table (fredt@users)
        *
        * @param  indexname
        * @throws  HsqlException
        */
       public void dropIndex(string indexname)
       {

           if (table.isIndexingMutable())
           {
               table.dropIndex(session, indexname);
           }
           else
           {
               int[] removeIndex = new int[] { table.getIndexIndex(indexname) };
               Table tn = table.moveDefinition(removeIndex, null, -1, 0);

               tn.moveData(session, table, -1, 0);
               tn.updateConstraintsTables(session, table, -1, 0);

               int i = table.database.schemaManager.getTableIndex(table);

               table.database.schemaManager.setTable(i, tn);

               table = tn;
           }

           table.database.schemaManager.removeIndexName(indexname,
                   table.getName());
           table.database.schemaManager.recompileViews(table);
       }

       /**
        *
        * @param  column
        * @param  colindex
        * @throws  HsqlException
        */
       public void retypeColumn(Column column, int colindex)
       {

           if (table.isText() && !table.isEmpty(session))
           {
               throw Trace.error(Trace.OPERATION_NOT_SUPPORTED);
           }

           table.database.schemaManager.checkColumnIsInView(table,
                   table.getColumn(colindex).columnName.name);
           table.checkColumnInCheckConstraint(
               table.getColumn(colindex).columnName.name);

           int[] dropIndexes = null;
           Table tn = table.moveDefinition(dropIndexes, column, colindex, 0);

           tn.moveData(session, table, colindex, 0);
           tn.updateConstraintsTables(session, table, colindex, 0);

           int i = table.database.schemaManager.getTableIndex(table);

           table.database.schemaManager.setTable(i, tn);

           table = tn;

           table.database.schemaManager.recompileViews(table);
       }

       /**
        *
        * @param  colIndex
        * @throws  HsqlException
        */
       public void dropColumn(int colIndex)
       {

           HsqlNameManager.HsqlName constNameRemove = null;

           if (table.isText() && !table.isEmpty(session))
           {
               throw Trace.error(Trace.OPERATION_NOT_SUPPORTED);
           }

           table.database.schemaManager.checkColumnIsInView(table,
                   table.getColumn(colIndex).columnName.name);
           table.checkColumnInCheckConstraint(
               table.getColumn(colIndex).columnName.name);

           Table tn = table;
           int[] dropIndexes = null;
           string colName = tn.getColumn(colIndex).columnName.name;

           tn.checkColumnInFKConstraint(colIndex);

           if (table.getPrimaryKey().Length == 1
                   && table.getPrimaryKey()[0] == colIndex)
           {
               table.checkDropIndex(table.getIndex(0).getName().name, null,
                                    true);

               constNameRemove = table.constraintList[0].getName();
               tn = table.moveDefinitionPK(null, false);
           }

           Constraint c = tn.getUniqueConstraintForColumns(new int[]{
                colIndex });

           if (c != null)
           {
               Index idx = c.getMainIndex();

               dropIndexes = new int[] { tn.getIndexIndex(idx.getName().name) };
               constNameRemove = c.getName();
           }

           tn = tn.moveDefinition(dropIndexes, null, colIndex, -1);

           tn.moveData(session, table, colIndex, -1);
           tn.updateConstraintsTables(session, table, colIndex, -1);

           int i = table.database.schemaManager.getTableIndex(table);

           table.database.schemaManager.setTable(i, tn);

           table = tn;

           table.database.schemaManager.recompileViews(table);

           if (constNameRemove != null)
           {
               table.removeConstraint(constNameRemove.name);
               table.database.schemaManager.removeConstraintName(
                   constNameRemove.name, table.getName());
           }
       }

       /**
        *
        * @param  column
        * @param  colIndex
        * @throws  HsqlException
        */
       public void addColumn(Column column, int colIndex)
       {

           if (table.isText() && !table.isEmpty(session))
           {
               throw Trace.error(Trace.OPERATION_NOT_SUPPORTED);
           }

           Table tn = table;

           tn = tn.moveDefinition(null, column, colIndex, 1);

           if (column.isPrimaryKey())
           {
               tn = tn.moveDefinitionPK(new int[] { colIndex }, true);
           }

           tn.moveData(session, table, colIndex, 1);
           tn.updateConstraintsTables(session, table, colIndex, 1);

           int i = table.database.schemaManager.getTableIndex(table);

           table.database.schemaManager.setTable(i, tn);

           table = tn;

           table.database.schemaManager.recompileViews(table);

           if (column.isPrimaryKey())
           {
               HsqlNameManager.HsqlName pkNameAdd = tn.makeSysPKName();
               Constraint newconstraint = new Constraint(pkNameAdd, table,
                   table.getPrimaryIndex(), Constraint.PRIMARY_KEY);

               table.addConstraint(newconstraint);
               table.database.schemaManager.registerConstraintName(
                   pkNameAdd.name, table.getName());
           }
       }

       /**
        *  Drop a named constraint
        *
        */
       public void dropConstraint(string name)
       {

           Constraint c = table.getConstraint(name);
           int ctype;

           if (c == null)
           {
               throw Trace.error(Trace.CONSTRAINT_NOT_FOUND,
                                 Trace.TableWorks_dropConstraint, new Object[] {
                    name, table.getName().name
                });
           }

           ctype = c.getType();

           if (ctype == Constraint.MAIN)
           {
               throw Trace.error(Trace.DROP_SYSTEM_CONSTRAINT);
           }

           if (ctype == Constraint.PRIMARY_KEY)
           {
               addOrDropPrimaryKey(null, false);
               table.removeConstraint(name);
           }
           else if (ctype == Constraint.FOREIGN_KEY)
           {
               dropFKConstraint(c);
           }
           else if (ctype == Constraint.UNIQUE)
           {
               HashSet<string> cset = new HashSet<string>();

               cset.Add(c.constName.name);

               // throw if the index for unique constraint is shared
               table.checkDropIndex(c.getMainIndex().getName().name, cset,
                                    false);

               // all is well if dropIndex throws for lack of resources
               dropIndex(c.getMainIndex().getName().name);
               table.removeConstraint(name);
           }
           else if (ctype == Constraint.CHECK)
           {
               table.removeConstraint(name);
           }

           table.database.schemaManager.removeConstraintName(name,
                   table.getName());
       }

       public void dropFKConstraint(Constraint c)
       {

           // drop the reference index, which is automatic and unused elsewhere
           Index constIndex = c.getRefIndex();

           // all is well if dropIndex throws for lack of resources
           dropIndex(constIndex.getName().name);

           Table mainTable = c.getMain();

           // MAIN constraint was created after REF, so delete first
           mainTable.removeConstraint(c.getPkName());
           table.removeConstraint(c.getFkName());
       }

       public void reTypeColumn(Column oldCol, Column newCol)
       {

           bool notallowed = false;
           int oldtype = oldCol.getType();
           int newtype = newCol.getType();

           switch (newtype)
           {

               case Types.BINARY:
               case Types.VARBINARY:
               case Types.LONGVARBINARY:
               case Types.OTHER:
               case Types.JAVA_OBJECT:
                   notallowed = !(newtype == oldtype || table.isEmpty(session));
                   break;
           }

           switch (oldtype)
           {

               case Types.BINARY:
               case Types.VARBINARY:
               case Types.LONGVARBINARY:
               case Types.OTHER:
               case Types.JAVA_OBJECT:
                   notallowed = !(newtype == oldtype || table.isEmpty(session));
                   break;

               case Types.TINYINT:
               case Types.SMALLINT:
               case Types.INTEGER:
               case Types.BIGINT:
               case Types.REAL:
               case Types.FLOAT:
               case Types.DOUBLE:
               case Types.NUMERIC:
               case Types.DECIMAL:
                   switch (newtype)
                   {

                       case Types.DATE:
                       case Types.TIME:
                       case Types.TIMESTAMP:
                           notallowed = !table.isEmpty(session);
                           break;
                       default:
                           break;
                   }
                   break;

               case Types.DATE:
               case Types.TIME:
               case Types.TIMESTAMP:
                   switch (newtype)
                   {

                       case Types.TINYINT:
                       case Types.SMALLINT:
                       case Types.INTEGER:
                       case Types.BIGINT:
                       case Types.REAL:
                       case Types.FLOAT:
                       case Types.DOUBLE:
                       case Types.NUMERIC:
                       case Types.DECIMAL:
                           notallowed = !table.isEmpty(session);
                           break;
                       default:
                           break;
                   }
                   break;
           }

           if (notallowed)
           {
               throw Trace.error(Trace.INVALID_CONVERSION);
           }

           int colIndex = table.getColumnNr(oldCol.columnName.name);

           if (table.getPrimaryKey().Length > 1)
           {

               // if there is a multi-column PK, do not change the PK attributes
               if (newCol.isIdentity())
               {
                   throw Trace.error(Trace.SECOND_PRIMARY_KEY);
               }

               newCol.setPrimaryKey(oldCol.isPrimaryKey());

               if (ArrayUtil.find(table.getPrimaryKey(), colIndex) != -1)
               {
                   newCol.setNullable(false);
               }
           }
           else if (table.hasPrimaryKey())
           {
               if (oldCol.isPrimaryKey())
               {
                   newCol.setPrimaryKey(true);
                   newCol.setNullable(false);
               }
               else if (newCol.isPrimaryKey())
               {
                   throw Trace.error(Trace.SECOND_PRIMARY_KEY);
               }
           }
           else if (newCol.isPrimaryKey())
           {
               throw Trace.error(Trace.PRIMARY_KEY_NOT_ALLOWED);
           }

           // apply and return if only metadata change is required
           if (newtype == oldtype && oldCol.isNullable() == newCol.isNullable()
                   && oldCol.getScale() == newCol.getScale()
                   && oldCol.isIdentity() == newCol.isIdentity()
                   && oldCol.identityIncrement == newCol.identityIncrement
                   && (oldCol.getSize() == newCol.getSize()
                       || (oldCol.getSize() < newCol.getSize()
                           && (oldtype == Types.VARCHAR
                               || oldtype == Types.DECIMAL
                               || oldtype == Types.NUMERIC))))
           {

               // size of some types may be increased with this command
               // default expressions can change
               oldCol.setType(newCol);
               oldCol.setDefaultExpression(newCol.getDefaultExpression());
               table.setColumnTypeVars(colIndex);
               table.resetDefaultsFlag();

               return;
           }

           table.database.schemaManager.checkColumnIsInView(table,
                   table.getColumn(colIndex).columnName.name);
           table.checkColumnInCheckConstraint(
               table.getColumn(colIndex).columnName.name);
           table.checkColumnInFKConstraint(colIndex);
           checkConvertColDataType(oldCol, newCol);
           retypeColumn(newCol, colIndex);
       }

       public void checkConvertColDataType(Column oldCol,
                                    Column newCol)
       {

           int colIndex = table.getColumnNr(oldCol.columnName.name);
           RowIterator it = table.getPrimaryIndex().firstRow(session);

           while (it.hasNext())
           {
               Row row = it.next();
               Object o = row.getData()[colIndex];

               Column.convertObject(session, o, newCol.getType(),
                                    newCol.getSize(), newCol.getScale());
           }
       }

       /**
        * performs the work for changing the nullability of a column
        */
       public void setColNullability(Column column,
                              bool nullable)
       {

           int colIndex = table.getColumnNr(column.columnName.name);

           if (nullable)
           {
               if (column.isPrimaryKey())
               {
                   throw Trace.error(Trace.TRY_TO_INSERT_NULL);
               }

               table.checkColumnInFKConstraint(colIndex, Constraint.SET_NULL);
           }
           else
           {
               RowIterator it = table.getPrimaryIndex().firstRow(session);

               while (it.hasNext())
               {
                   Row row = it.next();
                   Object o = row.getData()[colIndex];

                   if (o == null)
                   {
                       throw Trace.error(Trace.TRY_TO_INSERT_NULL);
                   }
               }
           }

           column.setNullable(nullable);
           table.setColumnTypeVars(colIndex);
       }

       /**
        * performs the work for changing the default value of a column
        */
       public void setColDefaultExpression(int colIndex,
                                    Expression def)
       {

           if (def == null)
           {
               table.checkColumnInFKConstraint(colIndex, Constraint.SET_DEFAULT);
           }

           table.setDefaultExpression(colIndex, def);
       }
    }
}
